import pandas as pd
import numpy as np
from datetime import datetime, timedelta


class InsuranceCostAnalyzer:
    """财产保险成本费用分析系统"""

    def __init__(self):
        """初始化分析器"""
        self.data = None
        self.indicators = {}

    def generate_sample_data(self, n=1000):
        """
        生成样本数据

        参数:
            n: 样本数量
        """
        # 生成日期数据 (近一年)
        end_date = datetime.now()
        start_date = end_date - timedelta(days=365)
        date_range = [start_date + timedelta(days=i) for i in range(365)]

        # 生成业务数据
        data = {
            'policy_id': [f'P{i:06d}' for i in range(1, n + 1)],
            'business_type': np.random.choice(['财险业务', '人身险业务', '再保财险业务', '再保人身险业务'], n),
            'policy_date': np.random.choice(date_range, n),
            'original_premium': np.random.lognormal(mean=10, sigma=0.5, size=n),  # 原保费收入
            'ceded_premium': np.random.lognormal(mean=8, sigma=0.5, size=n),  # 分出保费
            'claims_payment': np.random.lognormal(mean=9, sigma=0.6, size=n),  # 赔付支出
            'outstanding_claims_provision': np.random.lognormal(mean=8, sigma=0.5, size=n),  # 提取未决赔款准备金
            'outstanding_claims_reversal': np.random.lognormal(mean=7, sigma=0.5, size=n),  # 转回未决赔款准备金
            'unearned_premium_provision': np.random.lognormal(mean=8, sigma=0.5, size=n),  # 提取未到期责任准备金
            'unearned_premium_reversal': np.random.lognormal(mean=7, sigma=0.5, size=n),  # 转回未到期责任准备金
            'business_management_fee': np.random.lognormal(mean=7, sigma=0.5, size=n),  # 业务及管理费
            'commission_fee': np.random.lognormal(mean=7, sigma=0.5, size=n),  # 手续费及佣金
            'reinsurance_fee': np.random.lognormal(mean=7, sigma=0.5, size=n),  # 分保费用
            'insurance_tax': np.random.lognormal(mean=6, sigma=0.5, size=n),  # 保险业务营业税金及附加
            'reinsurance_fee_recovery': np.random.lognormal(mean=6, sigma=0.5, size=n),  # 摊回分保费用
            'ceded_premium_income': np.random.lognormal(mean=8, sigma=0.5, size=n),  # 分保费收入
        }

        # 创建DataFrame
        self.data = pd.DataFrame(data)

        # 添加年、月、周列
        self.data['year'] = self.data['policy_date'].dt.year
        self.data['month'] = self.data['policy_date'].dt.month
        self.data['week'] = self.data['policy_date'].dt.isocalendar().week

        return self.data

    def calculate_indicators(self, time_period='all', business_type=None):
        """
        计算成本费用指标

        参数:
            time_period: 时间周期 ('all', 'day', 'week', 'month', 'year')
            business_type: 业务类型 (None表示所有类型)
        """
        # 筛选数据
        filtered_data = self.data.copy()

        if business_type:
            filtered_data = filtered_data[filtered_data['business_type'] == business_type]

        # 根据时间周期分组
        if time_period == 'day':
            # 按日期和业务类型分组，但不包含日期列在聚合中
            group_cols = ['business_type']
            date_col = filtered_data['policy_date']
        elif time_period == 'week':
            group_cols = ['year', 'week', 'business_type']
        elif time_period == 'month':
            group_cols = ['year', 'month', 'business_type']
        elif time_period == 'year':
            group_cols = ['year', 'business_type']
        else:  # 'all'
            group_cols = ['business_type']

        # 确定需要聚合的数值列
        numeric_cols = [
            'original_premium', 'ceded_premium', 'claims_payment',
            'outstanding_claims_provision', 'outstanding_claims_reversal',
            'unearned_premium_provision', 'unearned_premium_reversal',
            'business_management_fee', 'commission_fee', 'reinsurance_fee',
            'insurance_tax', 'reinsurance_fee_recovery', 'ceded_premium_income'
        ]

        # 执行分组聚合
        grouped_data = filtered_data.groupby(group_cols)[numeric_cols].sum().reset_index()

        # 计算指标
        for _, row in grouped_data.iterrows():
            # 计算已赚保费
            earned_premium = (
                    row['original_premium'] -
                    row['ceded_premium'] -
                    row['unearned_premium_provision'] +
                    row['unearned_premium_reversal']
            )

            # 计算赔付支出净额
            net_claims = (
                    row['claims_payment'] +
                    row['outstanding_claims_provision'] -
                    row['outstanding_claims_reversal']
            )

            # 计算综合费用
            total_expenses = (
                    row['business_management_fee'] +
                    row['commission_fee'] +
                    row['reinsurance_fee'] +
                    row['insurance_tax'] -
                    row['reinsurance_fee_recovery']
            )

            # 计算各指标
            indicators = {
                '赔付率': (net_claims / earned_premium * 100) if earned_premium != 0 else 0,
                '综合费用率': (total_expenses / earned_premium * 100) if earned_premium != 0 else 0,
                '综合成本率': ((net_claims + total_expenses) / earned_premium * 100) if earned_premium != 0 else 0,
                '保费费用率': (row['business_management_fee'] / row['original_premium'] * 100) if row[
                                                                                                      'original_premium'] != 0 else 0,
                '手续费及佣金比率': (row['commission_fee'] / row['original_premium'] * 100) if row[
                                                                                                   'original_premium'] != 0 else 0,
                '分保费用比率': (row['reinsurance_fee'] / row['ceded_premium_income'] * 100) if row[
                                                                                                    'ceded_premium_income'] != 0 else 0,
            }

            # 构建索引
            if time_period == 'day':
                # 使用日期信息作为索引的一部分
                date_str = date_col.iloc[0].strftime('%Y-%m-%d')
                index = f"{date_str}_{row['business_type']}"
            elif time_period == 'week':
                index = f"{row['year']}年第{row['week']}周_{row['business_type']}"
            elif time_period == 'month':
                index = f"{row['year']}年{row['month']}月_{row['business_type']}"
            elif time_period == 'year':
                index = f"{row['year']}年_{row['business_type']}"
            else:
                index = row['business_type']

            self.indicators[index] = indicators

        return pd.DataFrame(self.indicators).T

    def export_to_excel(self, file_path='保险成本费用指标分析.xlsx'):
        """
        将分析结果导出到Excel

        参数:
            file_path: 输出文件路径
        """
        # 创建ExcelWriter对象
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
            # 导出原始数据
            self.data.to_excel(writer, sheet_name='原始数据', index=False)

            # 导出按日统计结果
            daily_indicators = self.calculate_indicators(time_period='day')
            daily_indicators.to_excel(writer, sheet_name='按日统计')

            # 导出按周统计结果
            weekly_indicators = self.calculate_indicators(time_period='week')
            weekly_indicators.to_excel(writer, sheet_name='按周统计')

            # 导出按月统计结果
            monthly_indicators = self.calculate_indicators(time_period='month')
            monthly_indicators.to_excel(writer, sheet_name='按月统计')

            # 导出按年统计结果
            yearly_indicators = self.calculate_indicators(time_period='year')
            yearly_indicators.to_excel(writer, sheet_name='按年统计')

            # 导出汇总统计结果
            all_indicators = self.calculate_indicators(time_period='all')
            all_indicators.to_excel(writer, sheet_name='汇总统计')

        print(f"分析结果已导出到 {file_path}")


# 使用示例
if __name__ == "__main__":
    # 创建分析器实例
    analyzer = InsuranceCostAnalyzer()

    # 生成样本数据
    data = analyzer.generate_sample_data(n=5000)
    print(f"生成了 {len(data)} 条样本数据")

    # 计算指标
    all_indicators = analyzer.calculate_indicators()
    print("计算完成的指标：")
    print(all_indicators.head())

    # 导出结果到Excel
    analyzer.export_to_excel()